from models.chinese_words_model import ChineseWordsModel
from utils.db import execute_pagination_query, execute_query, execute_update
from datetime import datetime
from models.textbooks_model import TextbooksModel

class BookService:
    def __init__(self):
        self.table_name = 'textbooks'
    
    def get_books(self, page=1, page_size=10, search_keyword=None):
        """分页获取书籍列表，支持关键词搜索"""
        # 构建查询SQL
        base_query = f"SELECT * FROM {self.table_name}"
        params = []
        
        # 如果有搜索关键词，添加WHERE条件
        if search_keyword:
            base_query += " WHERE phrase LIKE %s OR pinyin LIKE %s OR meaning LIKE %s"
            search_param = f"%{search_keyword}%"
            params = [search_param, search_param, search_param]
        
        
        # 执行分页查询
        result = execute_query(base_query, params)
        
        # 转换为模型对象
       # result['data'] = [TextbooksModel(**item).to_dict() for item in result['data']]
        
        return result
    
    def get_word_by_id(self, word_id):
        """根据ID获取单个词语"""
        query = f"SELECT * FROM {self.table_name} WHERE id = %s"
        result = execute_query(query, [word_id], fetch_one=True)
        
        if result:
            return ChineseWordsModel(**result).to_dict()
        return None
    
    def create_word(self, word_data):
        """创建新词语"""
        # 创建时自动设置时间字段
        current_time = datetime.now()
        word_data['created_at'] = current_time
        word_data['updated_at'] = current_time
        
        # 构建插入SQL
        fields = ', '.join(word_data.keys())
        placeholders = ', '.join(['%s'] * len(word_data))
        query = f"INSERT INTO {self.table_name} ({fields}) VALUES ({placeholders})"
        
        # 执行插入
        affected_rows = execute_update(query, list(word_data.values()))
        
        # 获取插入的ID
        if affected_rows > 0:
            last_id_query = "SELECT LAST_INSERT_ID() as id"
            last_id = execute_query(last_id_query, fetch_one=True)['id']
            return self.get_word_by_id(last_id)
        
        return None
    
    def update_word(self, word_id, word_data):
        """更新词语"""
        # 更新时自动设置updated_at字段
        word_data['updated_at'] = datetime.now()
        
        # 构建更新SQL
        set_clause = ', '.join([f"{key} = %s" for key in word_data.keys()])
        query = f"UPDATE {self.table_name} SET {set_clause} WHERE id = %s"
        
        # 执行更新
        params = list(word_data.values()) + [word_id]
        affected_rows = execute_update(query, params)
        
        if affected_rows > 0:
            return self.get_word_by_id(word_id)
        
        return None
    
    def delete_word(self, word_id):
        """删除词语"""
        query = f"DELETE FROM {self.table_name} WHERE id = %s"
        affected_rows = execute_update(query, [word_id])
        return affected_rows > 0